using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using SBPweb.Common.Session;
using System.Threading;

namespace SBPweb.Data.SQL2005
{

	/// <summary>
	/// This class provides cached SqlConnections, SqlTransactions and SqlDatabases
	/// </summary>
	public class SqlConnectionProvider
	{

		#region Private constants

		private const string SESSIONKEY_ConnectionProvider = "_SESSIONKEY_ConnectionProvider";

		#endregion

		#region Public static properties

		/// <summary>
		/// Session level SqlConnectionProvider instance
		/// </summary>
		public static SqlConnectionProvider SessionConnectionProvider
		{
			get
			{
				SqlConnectionProvider ret = SessionHandler.Get<SqlConnectionProvider>(SESSIONKEY_ConnectionProvider);
				if (ret == null)
				{
					ret = new SqlConnectionProvider();
					SessionHandler.Put<SqlConnectionProvider>(SESSIONKEY_ConnectionProvider, ret);
				}
				return ret;
			}
		}
		
		/// <summary>
		/// Thread level SqlConnectionProvider instance
		/// </summary>
		public static SqlConnectionProvider ThreadConnectionProvider
		{
			get
			{
				string threadConnectionProviderKey = string.Format("{0}_{1}", SESSIONKEY_ConnectionProvider, Thread.CurrentThread.ManagedThreadId.ToString());
				SqlConnectionProvider ret = SessionHandler.Get<SqlConnectionProvider>(threadConnectionProviderKey);
				if (ret == null)
				{
					ret = new SqlConnectionProvider();
					SessionHandler.Put<SqlConnectionProvider>(threadConnectionProviderKey, ret);
				}
				return ret;
			}
		}

		#endregion

		#region Private members

		private Dictionary<string, SqlDatabase> mDatabases;
		private Dictionary<string, SqlConnection> mConnections;
		private Dictionary<string, SqlTransaction> mTransactions;

		#endregion

		#region Constructor logic

		/// <summary>
		/// Creates a new instance of SqlConnectionProvider
		/// </summary>
		public SqlConnectionProvider()
		{
			mDatabases = new Dictionary<string, SqlDatabase>();
			mConnections = new Dictionary<string, SqlConnection>();
			mTransactions = new Dictionary<string, SqlTransaction>();
		}

		#endregion

		#region SqlDatabase

		/// <summary>
		/// Gets an SqlDatabase for the passed connectionString
		/// </summary>
		/// <param name="connectionString">Connection string to database</param>
		/// <returns>SqlDatabase instance</returns>
		public SqlDatabase GetSqlDatabase(string connectionString)
		{
			if (!string.IsNullOrEmpty(connectionString))
			{
				lock (mDatabases)
				{
					if (mDatabases.ContainsKey(connectionString))
					{
						return mDatabases[connectionString];
					}
					else
					{
						SqlDatabase database = DatabaseFactory.CreateDatabase(connectionString) as SqlDatabase;
						mDatabases.Add(connectionString, database);
						return database;
					}
				}
			}
			else
			{
				throw new NullReferenceException("GetSqlDatabase: connectionString cannot be null!");
			}
		}

		/// <summary>
		/// Removes an SqlDatabase instance from the cache by its connectionString
		/// </summary>
		/// <param name="connectionString">Connection string of SqlDatabase</param>
		public void RemoveSqlDatabase(string connectionString)
		{
			if (!string.IsNullOrEmpty(connectionString))
			{
				lock (mDatabases)
				{
					if (mDatabases.ContainsKey(connectionString))
					{
						mDatabases.Remove(connectionString);
					}
				}
			}
			else
			{
				throw new NullReferenceException("RemoveSqlDatabase: connectionString cannot be null!");
			}
		}

		/// <summary>
		/// Removes an SqlDatabase instance
		/// </summary>
		/// <param name="database">SqlDatabase</param>
		public void RemoveSqlDatabase(SqlDatabase database)
		{
			if (database != null)
			{
				RemoveSqlDatabase(database.ConnectionString);
			}
			else
			{
				throw new NullReferenceException("RemoveSqlDatabase: database cannot be null!");
			}
		}

		#endregion

		#region SqlConnection

		/// <summary>
		/// Gets an SqlConnection by its connection string
		/// </summary>
		/// <param name="connectionString">Connection string of SqlConnection</param>
		/// <returns>SqlConnection instance</returns>
		public SqlConnection GetSqlConnection(string connectionString)
		{
			if (!string.IsNullOrEmpty(connectionString))
			{
				lock (mConnections)
				{
					SqlConnection connection = null;
					if (mConnections.ContainsKey(connectionString))
					{
						connection = mConnections[connectionString];
						if (connection.State == System.Data.ConnectionState.Broken || connection.State == System.Data.ConnectionState.Closed)
						{
							mConnections.Remove(connectionString);
							connection = null;
						}
					}
					if (connection == null)
					{
						SqlDatabase database = GetSqlDatabase(connectionString);
						connection = database.OpenConnection() as SqlConnection;
						mConnections.Add(connectionString, connection);
					}
					return connection;
				}
			}
			else
			{
				throw new NullReferenceException("GetSqlConnection: connectionString cannot be null!");
			}
		}

		/// <summary>
		/// Removes an SqlConnection instance from the cache by its connectionString
		/// </summary>
		/// <param name="connectionString">Connection string of SqlConnection</param>
		public void RemoveSqlConnection(string connectionString)
		{
			if (!string.IsNullOrEmpty(connectionString))
			{
				lock (mConnections)
				{
					if (mConnections.ContainsKey(connectionString))
					{
						mConnections.Remove(connectionString);
						LogManager.WriteVerbose(this, string.Format("Connection on '{0}' removed!", connectionString), "Transaction");
					}
				}
			}
			else
			{
				throw new NullReferenceException("RemoveSqlConnection: connectionString cannot be null!");
			}
		}

		/// <summary>
		/// Removes an SqlConnection instance from the cache
		/// </summary>
		/// <param name="connection">SqlConnection connection</param>
		public void RemoveSqlConnection(SqlConnection connection)
		{
			if (connection != null)
			{
				RemoveSqlConnection(connection.ConnectionString);
			}
			else
			{
				throw new NullReferenceException("RemoveSqlConnection: connection cannot be null!");
			}
		}

		#endregion

		#region SqlTransaction

		/// <summary>
		/// Gets an SqlTransaction from the cache by its connection string
		/// </summary>
		/// <param name="connectionString">Connection string of database</param>
		/// <param name="createNew">if true: the method starts new transaction if the cache
		/// doesn't contains one for this connection string</param>
		/// <returns>SqlTransaction</returns>
		public SqlTransaction GetSqlTransaction(string connectionString, bool createNew)
		{
			if (!string.IsNullOrEmpty(connectionString))
			{
				lock (mTransactions)
				{
					SqlTransaction transaction = null;
					if (mTransactions.ContainsKey(connectionString))
					{
						transaction = mTransactions[connectionString];
						if (transaction.Connection == null || transaction.Connection.State == System.Data.ConnectionState.Broken || transaction.Connection.State == System.Data.ConnectionState.Closed)
						{
							mTransactions.Remove(connectionString);
							transaction = null;
						}
					}
					if (transaction == null) 
					{
						if (!createNew)
						{
							return null;
						}
						SqlConnection connection = GetSqlConnection(connectionString);
						transaction = connection.BeginTransaction();
						mTransactions.Add(connectionString, transaction);
					}
					return transaction;
				}
			}
			else
			{
				throw new NullReferenceException("GetSqlTransaction: connectionString cannot be null!");
			}
		}

		/// <summary>
		/// Gets an SqlTransaction from the cache by its connection string
		/// The method starts new transaction if the cache doesn't contains one for this connection string
		/// </summary>
		/// <param name="connectionString">Connection string of database</param>
		/// <returns>SqlTransaction</returns>
		public SqlTransaction GetSqlTransaction(string connectionString)
		{
			return GetSqlTransaction(connectionString, true);
		}

		/// <summary>
		/// Removes an SqlTransaction instance from the cache by its connection string
		/// </summary>
		/// <param name="connectionString">Connection string of transaction</param>
		public void RemoveSqlTransaction(string connectionString)
		{
			if (!string.IsNullOrEmpty(connectionString))
			{
				lock (mTransactions)
				{
					if (mTransactions.ContainsKey(connectionString))
					{
						mTransactions.Remove(connectionString);
						LogManager.WriteVerbose(this, string.Format("Transaction on '{0}' removed!", connectionString), "Transaction");
					}
				}
			}
			else
			{
				throw new NullReferenceException("RemoveSqlTransaction: connectionString cannot be null!");
			}
		}

		/// <summary>
		/// Removes an SqlTransaction instance from the cache by its connection string
		/// </summary>
		/// <param name="transaction">SqlTransaction transaction</param>
		public void RemoveSqlTransaction(SqlTransaction transaction)
		{
			if (transaction != null)
			{
				if (transaction.Connection != null)
				{
					RemoveSqlTransaction(transaction.Connection.ConnectionString);
				}
				else
				{
					throw new NullReferenceException("RemoveSqlTransaction: transaction.Connection cannot be null!");
				}
			}
			else
			{
				throw new NullReferenceException("RemoveSqlTransaction: transaction cannot be null!");
			}
		}

		#endregion

		#region Public methods
		
		/// <summary>
		/// This method closes all connections and clears the connection cache
		/// </summary>
		public void CloseAllConnection()
		{
            lock (mConnections)
            {
                //foreach (string key in mConnections.Keys)
                //{
                //    SqlConnection connection = mConnections[key];
                //    if (connection.State == System.Data.ConnectionState.Open)
                //    {
                //        connection.Close();
                //        LogManager.WriteVerbose(this, string.Format("Connection on '{0}' closed!", key), "Transaction");
                //    }
                //}
                mConnections.Clear();
            }
		}

		/// <summary>
		/// This method commits all transactions, closes all connections and 
		/// clears the connection cache and the transaction cache
		/// </summary>
		public void CommitAll()
		{
			lock (mTransactions)
			{
				foreach (string key in mTransactions.Keys)
				{
					SqlTransaction transaction = mTransactions[key];
					SqlConnection connection = mConnections[key];
					transaction.Commit();
					LogManager.WriteVerbose(this, string.Format("Transaction on '{0}' commited!", key), "Transaction");
                    //if (connection.State == System.Data.ConnectionState.Open)
                    //{
                    //    connection.Close();
                    //    LogManager.WriteVerbose(this, string.Format("Connection on '{0}' closed!", key), "Transaction");
                    //}
				}
				mConnections.Clear();
				mTransactions.Clear();
			}
		}

		/// <summary>
		/// This method rollbacks all transactions, closes all connections and 
		/// clears the connection cache and the transaction cache
		/// </summary>
		public void RollbackAll()
		{
			lock (mTransactions)
			{
				foreach (string key in mTransactions.Keys)
				{
					SqlTransaction transaction = mTransactions[key];
					SqlConnection connection = mConnections[key];
					transaction.Rollback();
					LogManager.WriteVerbose(this, string.Format("Transaction on '{0}' rolled back!", key), "Transaction");
                    //if (connection.State == System.Data.ConnectionState.Open)
                    //{
                    //    connection.Close();
                    //    LogManager.WriteVerbose(this, string.Format("Connection on '{0}' closed!", key), "Transaction");
                    //}
				}
				mConnections.Clear();
				mTransactions.Clear();
			}
		}

		#endregion

	}
}
